<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<%@ page import="javax.portlet.*"%>

<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet"%>

<%@ page import="java.sql.*" %>
<%@ page import="java.net.*" %>
<%@ page import="java.io.InputStream" %>
<%@ page import="java.util.Properties" %>
<%@ page import="java.text.SimpleDateFormat" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.text.NumberFormat" %>




<portlet:defineObjects />
<%
PortletPreferences prefs = renderRequest.getPreferences(); //get preferences
PortletURL actionURL = renderResponse.createActionURL();
%>
<%-- Checking whethere this is the first time --%>
<%
    String url = prefs.getValue("jiraUrl", "null");
    if(url.equals("null")){ // this is the first time
        actionURL.setParameter("edit", "link");
%>
        <%-- Give a link to edit --%>
        <a href="<%=actionURL.toString()%>">Set your JIRA Login here</a>
<% }else{
      String myJIRALogin =(String)renderRequest.getAttribute("JIRA_LOGIN");
      String myJIRAUrl =(String)renderRequest.getAttribute("JIRA_URL");
        
%>


   <div class="portlet-msg-info">
        Issue Count at URL <b>"<%=(String)renderRequest.getAttribute("JIRA_URL") %>"</b>
    </div>



    <%

    //Read the JIRA application database configuration values from the Administration.properties file.
    URL myURL=application.getResource("/WEB-INF/Administration.properties");
    InputStream in = myURL.openStream();
    Properties p = new Properties();
    p.load( in );

    String myurl = p.getProperty("url");
    String mydbName = p.getProperty("database");
    String mydriver = p.getProperty("driverClassName");
    String myuserName = p.getProperty("username");
    String mypassword = p.getProperty("password");

    //This is the URLs used at the links displayed in the portlet.
    String baseURL = myJIRAUrl+"/secure/Dashboard.jspa?";
    String baseNavigatorURL = myJIRAUrl+"/secure/IssueNavigator.jspa?";

    //Arrays to store the retrived project details from the database.
    String[] prjIDs = new String[20];
    String[] prjNames = new String[20];
    String[] prjKeys = new String[20];
    int meme =0;

    //Variables to store the issue counts.
    int assignedIssuesTotalCount = 0;
    int assignedIssuesClosedCount = 0;
    int assignedIssuesLastWeekCount = 0;
    int assignedIssuesOpenCount = 0;
    

    int reporterIssuesTotalCount = 0;
    int reporterIssuesClosedCount = 0;
    int reporterIssuesLastWeekCount = 0;
    int reporterIssuesOpenCount = 0;

    //Establish a connection with the JIRA application database.
    Connection conn = null;
    Class.forName(mydriver).newInstance();
    conn = DriverManager.getConnection(myurl+mydbName,myuserName,mypassword);

    //Retrieve all the projects in the JIRA application and store them in the arrays.
    try {
        Statement s = conn.createStatement ();
        ResultSet resultset = s.executeQuery ("SELECT ID, pname, pkey FROM project");

        while (resultset.next() )
        {
            String myID = resultset.getString("ID");
            String myName = resultset.getString("pname");
            String myKey = resultset.getString("pkey");


            prjIDs[meme] = myID;
            prjNames[meme] = myName;
            prjKeys[meme] = myKey;
            meme = meme+1;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    //Iterate through each project to retrieve the issue counts.
    for (int i = 0; i < meme; i++) {

        //At the beginning of each iteration last week counts should be zero.
        reporterIssuesLastWeekCount = 0;
        assignedIssuesLastWeekCount = 0;

        //Create an array of objects to store details about the JIRA projects.
        Object[] jiraProject = new Object[] {Integer.parseInt(prjIDs[i]), prjKeys[i], prjNames[i]};
        int projectId = (Integer)jiraProject[0];
	String projectKey = (String)jiraProject[1];
	String projectName = (String)jiraProject[2];

        //Derive the URLs to provide navigation to the issues.
        String jiraURL = baseURL + "reset=true&pid=" + projectId;
        String jiraNavigatorURL = baseNavigatorURL + "reset=true&pid=" + projectId;

        //Retrive the total number of issues assigned to the user under each project
        Statement s2 = conn.createStatement ();
        ResultSet resultset2 = s2.executeQuery ("SELECT COUNT(*) as assignedTotal FROM jiraissue WHERE ASSIGNEE='" + myJIRALogin + "' AND pkey LIKE '" + projectKey + "%'" );
        
        while (resultset2.next() )
        {
            assignedIssuesTotalCount = resultset2.getInt("assignedTotal");
        }

        //Retrive the number of issues assigned to the user and which are closed under each project
        Statement s3 = conn.createStatement ();
        ResultSet resultset3 = s3.executeQuery ("SELECT COUNT(*) as assignedClosed FROM jiraissue WHERE ASSIGNEE='" + myJIRALogin + "' AND pkey LIKE '" + projectKey + "%' AND RESOLUTION=1" );

        while (resultset3.next() )
        {
            assignedIssuesClosedCount = resultset3.getInt("assignedClosed");
        }

        //Calculate the number of issues assigned to the user and which are still open under each project
        assignedIssuesOpenCount = assignedIssuesTotalCount - assignedIssuesClosedCount;

         //Retrive the total number of issues reported by the user under each project
        Statement s4 = conn.createStatement ();
        ResultSet resultset4 = s4.executeQuery ("SELECT COUNT(*) as reportTotal FROM jiraissue WHERE REPORTER='" + myJIRALogin + "' AND pkey LIKE '" + projectKey + "%'" );

        while (resultset4.next() )
        {
            reporterIssuesTotalCount = resultset4.getInt("reportTotal");
        }
        //Retrive the number of issues reported by the user and which are closed under each project
        Statement s5 = conn.createStatement ();
        ResultSet resultset5 = s5.executeQuery ("SELECT COUNT(*) as reportClosed FROM jiraissue WHERE REPORTER='" + myJIRALogin + "' AND pkey LIKE '" + projectKey + "%' AND RESOLUTION=1" );

        while (resultset5.next() )
        {
            reporterIssuesClosedCount = resultset5.getInt("reportClosed");
        }

        //Calculate the number of issues reported by the user and which are still open under each project
       reporterIssuesOpenCount = reporterIssuesTotalCount - reporterIssuesClosedCount;

       //Retrieve the created date for each issue reported by the user.
       Statement s6 = conn.createStatement ();
        ResultSet resultset6 = s6.executeQuery ("SELECT CREATED FROM jiraissue WHERE REPORTER='" + myJIRALogin + "' AND pkey LIKE '" + projectKey + "%'" );
        while (resultset6.next() )
        {
            //Calculate the number of dates between the current date and the created date of the issue.
            String createdDate = resultset6.getString("CREATED");
            String[] date = createdDate.split(" ");
            String issuedate = date[0];

            String pattern = "yyyy-MM-dd";
            SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
            Date issueCreatedDate = dateFormat.parse(issuedate);
            Date currenDdate = new Date();
            dateFormat.format(currenDdate);
            long deltaDays = ( currenDdate.getTime() - issueCreatedDate.getTime() )/ (24 * 60 * 60 * 1000);
            //IF the issue is created in the last week increment that count.
            if(deltaDays<=7)
            {
                reporterIssuesLastWeekCount++;
            }
        }

        //Retrieve the created date for each issue assigned the user.
        Statement s7 = conn.createStatement ();
        ResultSet resultset7 = s7.executeQuery ("SELECT CREATED FROM jiraissue WHERE ASSIGNEE='" + myJIRALogin + "' AND pkey LIKE '" + projectKey + "%'" );
        while (resultset7.next() )
        {
            //Calculate the number of dates between the current date and the created date of the issue.
            String createdDate = resultset7.getString("CREATED");
            String[] date = createdDate.split(" ");
            String issuedate = date[0];

            String pattern = "yyyy-MM-dd";
            SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
            Date issueCreatedDate = dateFormat.parse(issuedate);
            Date currenDdate = new Date();
            dateFormat.format(currenDdate);
            long deltaDays = ( currenDdate.getTime() - issueCreatedDate.getTime() )/ (24 * 60 * 60 * 1000);

            //IF the issue is created in the last week increment that count.
            if(deltaDays<=7)
            {
                assignedIssuesLastWeekCount++;
            }
        }
        


        //Create 2D object array for each row of the project details.
        Object[][] jiraProjectRowsArray = new Object[][] {
	new Object[] {
		"Assigned-Issues",
                jiraNavigatorURL +"&assigneeSelect=" +"issue_current_user" +"&resolution=-1"
	},
	new Object[] {
		"Open", request.getContextPath() + "/images/status_open.png",
		jiraNavigatorURL + "&assigneeSelect=specificuser&assignee=" + myJIRALogin + "&resolution=-1",
		new Integer(assignedIssuesOpenCount), new Integer(assignedIssuesTotalCount)
	},
	new Object[] {
		"Closed", request.getContextPath() + "/images/status_closed.png",
		jiraNavigatorURL + "&assigneeSelect=specificuser&assignee=" + myJIRALogin + "&status=" + 6,
		new Integer(assignedIssuesClosedCount), new Integer(assignedIssuesTotalCount)
	},
	new Object[] {
		"Last-Week", request.getContextPath() + "/images/calendar.png",
		jiraNavigatorURL + "&assigneeSelect=specificuser&assignee=" + myJIRALogin+ "&updated:previous=-1w",
		new Integer(assignedIssuesLastWeekCount), new Integer(assignedIssuesTotalCount)
	},
	new Object[] {
		"Reported-Issues",
                jiraNavigatorURL+"&reporterSelect=" + "issue_current_user"
	},
	new Object[] {
		"Open", request.getContextPath() + "/images/status_open.png",
		jiraNavigatorURL + "&reporterSelect=specificuser&reporter=" + myJIRALogin + "&resolution=-1",
		new Integer(reporterIssuesOpenCount), new Integer(reporterIssuesTotalCount)
	},
	new Object[] {
		"Closed", request.getContextPath() + "/images/status_closed.png",
		jiraNavigatorURL + "&reporterSelect=specificuser&reporter=" + myJIRALogin + "&status=" + 6,
		new Integer(reporterIssuesClosedCount), new Integer(reporterIssuesTotalCount)
	},
	new Object[] {
		"Last-Week", request.getContextPath() + "/images/calendar.png",
		jiraNavigatorURL + "&reporterSelect=specificuser&reporter=" + myJIRALogin + "&updated:previous=-1w",
		new Integer(reporterIssuesLastWeekCount), new Integer(reporterIssuesTotalCount)
	}
};%>
        <hr style="border-bottom: dotted 1px"/>
        <div class="project-title">
	<b><a href="<%= myJIRAUrl %>/browse/<%= projectKey %>" target="_blank"><%= projectName %></a></b>
</div>

<table class="jira-summary lfr-table">
<%
    //Iterate through each object and retrive the values which are necessary to dispay in the portlet.
    for (int j = 0; j < jiraProjectRowsArray.length; j++) {
	Object[] jiraProjectRows = jiraProjectRowsArray[j];

	if (jiraProjectRows.length == 2) {
		String message = (String)jiraProjectRows[0];
		String jurl = (String)jiraProjectRows[1];
%>

                <tr>
                     <td colspan="2"><a href="<% out.println(jurl); %>"><% out.println(message); %></a></td>
                </tr>

<%
	}
	else {
            String message = (String)jiraProjectRows[0];
            String icon = (String)jiraProjectRows[1];
            String jurl =  (String)jiraProjectRows[2] ;
            int curCount = ((Integer)jiraProjectRows[3]).intValue();
            int totalCount = ((Integer)jiraProjectRows[4]).intValue();

            //Calculate the percentage of each issue category.
            double ratio = (double)curCount / (double)totalCount;
            if (totalCount == 0) {
                ratio = 0;
            }

            Double percent = new Double(ratio);
            NumberFormat percentFormatter;
            String percentOut;
            percentFormatter = NumberFormat.getPercentInstance();
            percentOut = percentFormatter.format(percent);
%>


                <tr class="portlet-section-body results-row">
                    <td width="10%"><% out.println("<img src="+ icon); %></td>
                    <td><a href="<% out.println(jurl); %>"><% out.println(message); %></a></td>
                    <td><%= curCount %></td>
                    <td width="90%">
                        <div style= "background-color:#A8ADD4; width:<%=percentOut %>"/>
                        <p><%=percentOut %></p>
                    </td>
                </tr>
<%
	}
}
%>

    </table>

<%
    }
%>

    <%-- Footer to link to JIRA --%>
    <hr style="border-bottom: dotted 1px"/>
    <div class="portlet-section-footer" >
        Go To: <a href="<%=url%>" >JIRA</a>
    </div>
<% } %>